package com.example.memosystem.mapper;

import com.example.memosystem.model.Category;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface CategoryMapper {
    
    @Insert("INSERT INTO category (user_id, name, color, create_time, update_time) VALUES (#{userId}, #{name}, #{color}, #{createTime}, #{updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertCategory(Category category);
    
    @Update("UPDATE category SET name = #{name}, color = #{color}, update_time = NOW() WHERE id = #{id} AND user_id = #{userId}")
    int updateCategory(Category category);
    
    @Delete("DELETE FROM category WHERE id = #{id} AND user_id = #{userId}")
    int deleteCategory(@Param("id") Integer id, @Param("userId") Integer userId);
    
    @Select("SELECT c.*, (SELECT COUNT(*) FROM memo m WHERE m.category_id = c.id AND m.delete_time IS NULL) as memo_count " +
            "FROM category c WHERE c.id = #{id} AND c.user_id = #{userId}")
    Category findById(@Param("id") Integer id, @Param("userId") Integer userId);
    
    @Select("SELECT c.*, (SELECT COUNT(*) FROM memo m WHERE m.category_id = c.id AND m.delete_time IS NULL) as memo_count " +
            "FROM category c WHERE c.user_id = #{userId} ORDER BY c.create_time DESC")
    List<Category> findAllByUserId(Integer userId);
}